<!doctype html><html><head><meta charset=utf-8><meta name=viewport content="width=device-width,initial-scale=1"><meta name=robots content="noodp"><meta http-equiv=x-ua-compatible content="IE=edge, chrome=1"><title>PostgreSQL - 日期函数汇总 - Yulin Lewis' Blog</title><meta name=keywords content="雨临Lewis,Java,hugo,hexo,博客"><meta name=Description content="不想当写手的码农不是好咸鱼_(xз」∠)_"><meta property="og:title" content="PostgreSQL - 日期函数汇总"><meta property="og:description" content="比较两个日期之间的时间差超过N个小时
在PostgreSQL中，两个时间戳相减会得到一个interval类型的结果，如下：


1
2


select now() - '2021-03-28 15:47:07'; --0 years 0 mons 2 days 0 hours 1 mins 15.081206 secs
select '2021-03-28 15:47:07' - now(); --0 years 0 mons -2 days 0 hours -3 mins -17.692835 secs"><meta property="og:type" content="article"><meta property="og:url" content="https://lewky.cn/posts/postgresql-date/"><meta property="og:image" content="https://lewky.cn/logo.png"><meta property="article:published_time" content="2018-12-19T22:34:12+08:00"><meta property="article:modified_time" content="2022-01-09T22:34:12+08:00"><meta name=twitter:card content="summary_large_image"><meta name=twitter:image content="https://lewky.cn/logo.png"><meta name=twitter:title content="PostgreSQL - 日期函数汇总"><meta name=twitter:description content="比较两个日期之间的时间差超过N个小时
在PostgreSQL中，两个时间戳相减会得到一个interval类型的结果，如下：


1
2


select now() - '2021-03-28 15:47:07'; --0 years 0 mons 2 days 0 hours 1 mins 15.081206 secs
select '2021-03-28 15:47:07' - now(); --0 years 0 mons -2 days 0 hours -3 mins -17.692835 secs"><meta name=application-name content="雨临Lewis的博客"><meta name=apple-mobile-web-app-title content="雨临Lewis的博客"><meta name=theme-color content="#ffffff"><meta name=msapplication-TileColor content="#da532c"><link rel="shortcut icon" type=image/x-icon href=/favicon.ico><link rel=icon type=image/png sizes=32x32 href=/favicon-32x32.png><link rel=icon type=image/png sizes=16x16 href=/favicon-16x16.png><link rel=apple-touch-icon sizes=180x180 href=/apple-touch-icon.png><link rel=mask-icon href=/safari-pinned-tab.svg color=#5bbad5><link rel=manifest href=/site.webmanifest><link rel=canonical href=https://lewky.cn/posts/postgresql-date/><link rel=prev href=https://lewky.cn/posts/f20deee6.html/><link rel=next href=https://lewky.cn/posts/eee6409f.html/><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/normalize.css@8.0.1/normalize.min.css><link rel=stylesheet href=/css/style.min.css><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.13.0/css/all.min.css><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/animate.css@3.7.2/animate.min.css><script type=application/ld+json>{"@context":"http://schema.org","@type":"BlogPosting","headline":"PostgreSQL - 日期函数汇总","inLanguage":"","mainEntityOfPage":{"@type":"WebPage","@id":"https:\/\/lewky.cn\/posts\/postgresql-date\/"},"genre":"posts","keywords":"PostgreSQL, 工作记录","wordcount":969,"url":"https:\/\/lewky.cn\/posts\/postgresql-date\/","datePublished":"2018-12-19T22:34:12+08:00","dateModified":"2022-01-09T22:34:12+08:00","license":"This work is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License.","publisher":{"@type":"Organization","name":"雨临Lewis","logo":"https:\/\/lewky.cn\/images\/avatar.jpg"},"author":{"@type":"Person","name":"雨临Lewis"},"description":""}</script></head><body header-desktop=fixed header-mobile=auto><script type=text/javascript>(window.localStorage&&localStorage.getItem('theme')?localStorage.getItem('theme')==='dark':('auto'==='auto'?window.matchMedia('(prefers-color-scheme: dark)').matches:'auto'==='dark'))&&document.body.setAttribute('theme','dark');</script><div id=mask></div><div class=wrapper><header class=desktop id=header-desktop><div class=header-wrapper><a href=https://github.com/lewky class=github-corner target=_blank title="Follow me on GitHub" aria-label="Follow me on GitHub"><svg width="3.5rem" height="3.5rem" viewBox="0 0 250 250" style="fill:#70b7fd;color:#fff;position:absolute;top:0;border:0;left:0;transform:scale(-1,1)" aria-hidden="true"><path d="M0 0 115 115h15l12 27L250 250V0z"/><path d="M128.3 109C113.8 99.7 119 89.6 119 89.6 122 82.7 120.5 78.6 120.5 78.6 119.2 72 123.4 76.3 123.4 76.3 127.3 80.9 125.5 87.3 125.5 87.3 122.9 97.6 130.6 101.9 134.4 103.2" fill="currentcolor" style="transform-origin:130px 106px" class="octo-arm"/><path d="M115 115C114.9 115.1 118.7 116.5 119.8 115.4L133.7 101.6C136.9 99.2 139.9 98.4 142.2 98.6 133.8 88 127.5 74.4 143.8 58 148.5 53.4 154 51.2 159.7 51 160.3 49.4 163.2 43.6 171.4 40.1 171.4 40.1 176.1 42.5 178.8 56.2 183.1 58.6 187.2 61.8 190.9 65.4 194.5 69 197.7 73.2 200.1 77.6 213.8 80.2 216.3 84.9 216.3 84.9 212.7 93.1 206.9 96 205.4 96.6 205.1 102.4 203 107.8 198.3 112.5 181.9 128.9 168.3 122.5 157.7 114.1 157.9 116.9 156.7 120.9 152.7 124.9L141 136.5C139.8 137.7 141.6 141.9 141.8 141.8z" fill="currentcolor" class="octo-body"/></svg></a><div class=header-title><a href=/ title="Yulin Lewis' Blog"><span class=header-title-pre><i class="fas fa-fw fa-atom"></i></span>雨临Lewis</a></div><div class=menu><div class=menu-inner><div class=dropdown><a href=/posts/ class="menu-item menu-more dropbtn" title=点击查看所有文章><i class="fas fa-fw fa-archive"></i>归档</a><div class="menu-more-content dropdown-content"><a href=/categories/><i class="fas fa-fw fa-th"></i>分类 </a><a href=/tags/><i class="fas fa-fw fa-tag"></i>标签 </a><a href=/hot/ title=文章热度Top15><i class="fas fa-fw fa-fire"></i>热度 </a><a href=/donation/ title=感谢打赏，老板大气~><i class="fas fa-fw fa-donate"></i>打赏 </a><a href=/about/><i class="fas fa-fw fa-at"></i>关于</a></div></div><a class=menu-item href=/friends/ title=欢迎申请友链><i class="fas fa-fw fa-link"></i>友链</a><div class=dropdown><a href=javascript:void(0); class="menu-item menu-more dropbtn" title=一些有意思的东东~><i class="fas fa-fw fa-fan fa-spin"></i>趣味</a><div class="menu-more-content dropdown-content"><a href=/funny/high/ title="前方高能♂ 小心外放！" target=_blank rel=noopener><i class="fas fa-fw fa-dizzy"></i>燥起来！ </a><a href=/funny/mikutap/ title=初音未来音乐游戏 target=_blank rel=noopener><i class="fas fa-fw fa-music"></i>在线打碟 </a><a href=/funny/catch-the-cat/ title=逮住那只猫!><i class="fas fa-fw fa-cat"></i>抓住猫咪</a></div></div><div class=dropdown><a href=/ class="menu-item menu-more dropbtn" title=回到首页><i class="fas fa-fw fa-atom"></i>站点</a><div class="menu-more-content dropdown-content"><a href=/bbs/ title=来留言吧~><i class="fas fa-fw fa-comment"></i>公告留言 </a><a href=https://seo.chinaz.com target=_blank rel=noopener><i class="fas fa-fw fa-chart-line"></i>SEO查询 </a><a href=https://www.ping.cn/http/lewky.cn target=_blank rel=noopener><i class="fas fa-fw fa-bezier-curve"></i>网站测速 </a><a href=/posts/e62c38c4.html/><i class="fas fa-fw fa-cog fa-spin"></i>建站日志</a></div></div><div class=dropdown><a href=javascript:void(0); class="menu-item menu-more dropbtn"><i class="fas fa-fw fa-book"></i>文档</a><div class="menu-more-content dropdown-content"><a href=https://gohugo.io/documentation/ target=_blank rel=noopener><i class="fas fa-fw fa-star"></i>Hugo文档 </a><a href=https://javanote.doc.lewky.cn/ title=尚在完善中~ target=_blank rel=noopener><i class="fab fa-fw fa-java"></i>Java 笔记</a></div></div><a class=menu-item href=/search/ title=渲染搜索结果需要等待几秒钟~><i class="fas fa-fw fa-search"></i>搜索
</a><span class="menu-item delimiter"></span><a href=https://travellings.link target=_blank class=menu-item rel=noopener title=开往-友链接力><i class="fas fa-fw fa-subway"></i></a><a href=https://rssblog.vercel.app/ class=menu-item target=_blank rel=noopener title=RSSBlog><i class="fas fa-fw fa-inbox"></i></a><a href=https://github.com/lewky class=menu-item target=_blank rel=noopener title=GitHub><i class="fab fa-fw fa-github"></i></a></div></div></div></header><header class=mobile id=header-mobile><div class=header-container><div class=header-wrapper><div class=header-title><a href=/ title="Yulin Lewis' Blog"><span class=header-title-pre><i class="fas fa-fw fa-atom"></i></span>雨临Lewis</a></div><div class=menu-toggle id=menu-toggle-mobile><span></span><span></span><span></span></div></div><div class=menu id=menu-mobile><div class=dropdown><a href=/posts/ class="menu-item menu-more dropbtn" title=点击查看所有文章><i class="fas fa-fw fa-archive"></i>归档</a><div class="menu-more-content dropdown-content"><a href=/categories/><i class="fas fa-fw fa-th"></i>分类 </a><a href=/tags/><i class="fas fa-fw fa-tag"></i>标签 </a><a href=/hot/ title=文章热度Top15><i class="fas fa-fw fa-fire"></i>热度 </a><a href=/donation/ title=感谢打赏，老板大气~><i class="fas fa-fw fa-donate"></i>打赏 </a><a href=/about/><i class="fas fa-fw fa-at"></i>关于</a></div></div><a class=menu-item href=/friends/ title=欢迎申请友链><i class="fas fa-fw fa-link"></i>友链</a><div class=dropdown><a href=javascript:void(0); class="menu-item menu-more dropbtn" title=一些有意思的东东~><i class="fas fa-fw fa-fan fa-spin"></i>趣味</a><div class="menu-more-content dropdown-content"><a href=/funny/high/ title="前方高能♂ 小心外放！" target=_blank rel=noopener><i class="fas fa-fw fa-dizzy"></i>燥起来！ </a><a href=/funny/mikutap/ title=初音未来音乐游戏 target=_blank rel=noopener><i class="fas fa-fw fa-music"></i>在线打碟 </a><a href=/funny/catch-the-cat/ title=逮住那只猫!><i class="fas fa-fw fa-cat"></i>抓住猫咪</a></div></div><div class=dropdown><a href=/ class="menu-item menu-more dropbtn" title=回到首页><i class="fas fa-fw fa-atom"></i>站点</a><div class="menu-more-content dropdown-content"><a href=/bbs/ title=来留言吧~><i class="fas fa-fw fa-comment"></i>公告留言 </a><a href=https://seo.chinaz.com target=_blank rel=noopener><i class="fas fa-fw fa-chart-line"></i>SEO查询 </a><a href=https://www.ping.cn/http/lewky.cn target=_blank rel=noopener><i class="fas fa-fw fa-bezier-curve"></i>网站测速 </a><a href=/posts/e62c38c4.html/><i class="fas fa-fw fa-cog fa-spin"></i>建站日志</a></div></div><div class=dropdown><a href=javascript:void(0); class="menu-item menu-more dropbtn"><i class="fas fa-fw fa-book"></i>文档</a><div class="menu-more-content dropdown-content"><a href=https://gohugo.io/documentation/ target=_blank rel=noopener><i class="fas fa-fw fa-star"></i>Hugo文档 </a><a href=https://javanote.doc.lewky.cn/ title=尚在完善中~ target=_blank rel=noopener><i class="fab fa-fw fa-java"></i>Java 笔记</a></div></div><a class=menu-item href=/search/ title=渲染搜索结果需要等待几秒钟~><i class="fas fa-fw fa-search"></i>搜索</a>
<a href=https://travellings.link target=_blank class=menu-item rel=noopener title=开往-友链接力><i class="fas fa-fw fa-subway"></i></a><a href=https://rssblog.vercel.app/ class=menu-item target=_blank rel=noopener title=RSSBlog><i class="fas fa-fw fa-inbox"></i></a><a href=https://github.com/lewky class=menu-item target=_blank rel=noopener title=GitHub><i class="fab fa-fw fa-github"></i></a></div></div></header><div class="search-dropdown desktop"><div id=search-dropdown-desktop></div></div><div class="search-dropdown mobile"><div id=search-dropdown-mobile></div></div><main class=main><div class=container><div class=toc id=toc-auto><h2 class=toc-title>目录</h2><div class=toc-content id=toc-content-auto></div></div><article class="page single"><h1 class="single-title animated flipInX">PostgreSQL - 日期函数汇总</h1><div class=post-meta><div class=post-meta-line><span class=post-author><a href=/ title=Author rel=author class=author><i class="fas fa-user-circle fa-fw"></i>雨临Lewis</a></span>&nbsp;<span class=post-category>收录于 <a href=/categories/postgresql/><i class="far fa-folder fa-fw"></i>PostgreSQL</a></span></div><div class=post-meta-line><i class="far fa-calendar fa-fw"></i>&nbsp;<time datetime=2018-12-19>2018-12-19</time>&nbsp;<i class="far fa-calendar-plus fa-fw"></i>&nbsp;<time datetime=2022-01-09>2022-01-09</time>&nbsp;<i class="fas fa-pencil-alt fa-fw"></i>&nbsp;约 969 字
<i class="far fa-clock fa-fw"></i>&nbsp;预计阅读 2 分钟&nbsp;<span id=/posts/postgresql-date/ class=leancloud_visitors data-flag-title="PostgreSQL - 日期函数汇总">
<i class="far fa-eye fa-fw"></i>&nbsp;<span class=leancloud-visitors-count></span>&nbsp;次阅读
</span>&nbsp;
<a href=#comments id=post-meta-vcount title=查看评论><i class="fas fa-comment fa-fw"></i>&nbsp;<span id=/posts/postgresql-date/ class=waline-comment-count></span>&nbsp;条评论</a></div></div><div class="details toc" id=toc-static kept><div class="details-summary toc-title"><span>目录</span>
<span><i class="details-icon fas fa-angle-right"></i></span></div><div class="details-content toc-content" id=toc-content-static><nav id=TableOfContents><ol><li><a href=#比较两个日期之间的时间差超过n个小时>比较两个日期之间的时间差超过N个小时</a></li><li><a href=#比较日期时隐藏的坑>比较日期时隐藏的坑</a></li><li><a href=#extract函数对日期进行处理>EXTRACT函数对日期进行处理</a></li><li><a href=#去掉日期的毫秒值>去掉日期的毫秒值</a></li><li><a href=#补充epoch新纪元时间>补充：epoch新纪元时间</a></li><li><a href=#参考链接>参考链接</a></li></ol></nav></div></div><div class=content id=content><h2 id=比较两个日期之间的时间差超过n个小时>比较两个日期之间的时间差超过N个小时</h2><p>在PostgreSQL中，两个时间戳相减会得到一个<code>interval</code>类型的结果，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>now</span><span class=p>()</span> <span class=o>-</span> <span class=s1>&#39;2021-03-28 15:47:07&#39;</span><span class=p>;</span> <span class=c1>--0 years 0 mons 2 days 0 hours 1 mins 15.081206 secs
</span><span class=c1></span><span class=k>select</span> <span class=s1>&#39;2021-03-28 15:47:07&#39;</span> <span class=o>-</span> <span class=n>now</span><span class=p>();</span> <span class=c1>--0 years 0 mons -2 days 0 hours -3 mins -17.692835 secs
</span></code></pre></td></tr></table></div></div><p>通过对比两个<code>interval</code>类型即可得到我们想要的结果，PostgreSQL会自行对<code>interval</code>类型进行处理，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span><span class=lnt>3
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=nb>interval</span> <span class=s1>&#39;0 years 100 mons 2 days 0 hours&#39;</span> <span class=o>&gt;</span> <span class=nb>interval</span> <span class=s1>&#39;4years&#39;</span><span class=p>;</span> <span class=c1>--true
</span><span class=c1></span>
<span class=k>select</span> <span class=n>now</span><span class=p>()</span> <span class=o>-</span> <span class=s1>&#39;2021-03-28 15:47:07&#39;</span> <span class=o>&gt;</span> <span class=nb>interval</span> <span class=s1>&#39;4days&#39;</span><span class=p>;</span> <span class=c1>--false
</span></code></pre></td></tr></table></div></div><h2 id=比较日期时隐藏的坑>比较日期时隐藏的坑</h2><p>PostgreSQl在比较两个timestamp时，隐藏了一个坑：在用DBeaver查看数据时，这个timestamp的值是看不到毫秒值的，在比较的时候可能不小心就被毫秒值坑了，如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=o>*</span> <span class=k>from</span> <span class=n>cnt_item</span> <span class=k>where</span> <span class=n>updated_on</span> <span class=o>&gt;</span> <span class=s1>&#39;2019-05-13 15:49:26&#39;</span><span class=p>;</span>
</code></pre></td></tr></table></div></div><p>上述的条件是可能查出来更新日期为<code>2019-05-13 15:49:26</code>的数据的，原因是上诉条件等价于<code>updated_on > '2019-05-13 15:49:26 000'</code>，有部分数据的毫秒值虽然看不到，但却大于条件中的毫秒值，因此会造成查询出问题的错觉。</p><p>在查询日期时，应该尽量根据实际情况将秒值增大一秒或变小一秒。</p><h2 id=extract函数对日期进行处理>EXTRACT函数对日期进行处理</h2><p>语法如下：</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-fallback data-lang=fallback>EXTRACT(type FROM date)
</code></pre></td></tr></table></div></div><p><code>data</code>是日期，也可以是时间间距类型<code>interval</code>。这里的<code>type</code>需要指定为以下的值之一：</p><table><thead><tr><th align=left>type</th><th align=left>value</th></tr></thead><tbody><tr><td align=left>day</td><td align=left>返回日期值</td></tr><tr><td align=left>month</td><td align=left>返回月份值</td></tr><tr><td align=left>year</td><td align=left>返回年份值</td></tr><tr><td align=left>doy（day of year）</td><td align=left>返回年中的第几天</td></tr><tr><td align=left>dow（day of week）</td><td align=left>返回星期几</td></tr><tr><td align=left>quarter</td><td align=left>返回季度</td></tr><tr><td align=left>epoch</td><td align=left>将当前日期转化为秒值</td></tr></tbody></table><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt> 1
</span><span class=lnt> 2
</span><span class=lnt> 3
</span><span class=lnt> 4
</span><span class=lnt> 5
</span><span class=lnt> 6
</span><span class=lnt> 7
</span><span class=lnt> 8
</span><span class=lnt> 9
</span><span class=lnt>10
</span><span class=lnt>11
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>now</span><span class=p>();</span> <span class=c1>--2021-03-30 17:21:50
</span><span class=c1></span>
<span class=k>select</span> <span class=k>extract</span><span class=p>(</span><span class=k>day</span> <span class=k>from</span><span class=p>(</span><span class=n>now</span><span class=p>()));</span> <span class=c1>--30
</span><span class=c1></span><span class=k>select</span> <span class=k>extract</span><span class=p>(</span><span class=k>month</span> <span class=k>from</span><span class=p>(</span><span class=n>now</span><span class=p>()));</span> <span class=c1>--3
</span><span class=c1></span><span class=k>select</span> <span class=k>extract</span><span class=p>(</span><span class=k>year</span> <span class=k>from</span><span class=p>(</span><span class=n>now</span><span class=p>()));</span> <span class=c1>--2021
</span><span class=c1></span><span class=k>select</span> <span class=k>extract</span><span class=p>(</span><span class=n>doy</span> <span class=k>from</span><span class=p>(</span><span class=n>now</span><span class=p>()));</span> <span class=c1>--89
</span><span class=c1></span><span class=k>select</span> <span class=k>extract</span><span class=p>(</span><span class=n>dow</span> <span class=k>from</span><span class=p>(</span><span class=n>now</span><span class=p>()));</span> <span class=c1>--2
</span><span class=c1></span><span class=k>select</span> <span class=k>extract</span><span class=p>(</span><span class=n>quarter</span> <span class=k>from</span><span class=p>(</span><span class=n>now</span><span class=p>()));</span> <span class=c1>--1
</span><span class=c1></span>
<span class=k>select</span> <span class=k>extract</span><span class=p>(</span><span class=n>epoch</span> <span class=k>from</span> <span class=s1>&#39;01:00:06.678&#39;</span><span class=p>::</span><span class=n>time</span><span class=p>);</span> <span class=c1>--3606.678
</span><span class=c1></span><span class=k>select</span> <span class=k>extract</span><span class=p>(</span><span class=n>epoch</span> <span class=k>from</span><span class=p>(</span><span class=nb>interval</span> <span class=s1>&#39;0 years 0 mons 0 days 0 hours 10 mins 10.00 secs&#39;</span><span class=p>));</span> <span class=c1>--610
</span></code></pre></td></tr></table></div></div><p>如果需要把时间转化成毫秒值，直接把结果乘以1000就行。</p><h2 id=去掉日期的毫秒值>去掉日期的毫秒值</h2><p>向下取整函数<code>floor()</code></p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>floor</span><span class=p>(</span><span class=k>extract</span><span class=p>(</span><span class=n>epoch</span> <span class=k>from</span> <span class=s1>&#39;03:21:06.678&#39;</span><span class=p>::</span><span class=n>time</span><span class=p>));</span>
</code></pre></td></tr></table></div></div><p>向上取整函数<code>ceil()</code>或<code>ceiling()</code>，这两个一样的</p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span><span class=lnt>2
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>ceil</span><span class=p>(</span><span class=k>extract</span><span class=p>(</span><span class=n>epoch</span> <span class=k>from</span> <span class=s1>&#39;03:21:06.678&#39;</span><span class=p>::</span><span class=n>time</span><span class=p>));</span>
<span class=k>select</span> <span class=n>ceiling</span><span class=p>(</span><span class=k>extract</span><span class=p>(</span><span class=n>epoch</span> <span class=k>from</span> <span class=s1>&#39;03:21:06.678&#39;</span><span class=p>::</span><span class=n>time</span><span class=p>));</span>
</code></pre></td></tr></table></div></div><p>四舍五入函数<code>round()</code></p><div class=highlight><div class=chroma><table class=lntable><tr><td class=lntd><pre class=chroma><code><span class=lnt>1
</span></code></pre></td><td class=lntd><pre class=chroma><code class=language-sql data-lang=sql><span class=k>select</span> <span class=n>round</span><span class=p>(</span><span class=k>extract</span><span class=p>(</span><span class=n>epoch</span> <span class=k>from</span> <span class=s1>&#39;03:21:06.678&#39;</span><span class=p>::</span><span class=n>time</span><span class=p>));</span>
</code></pre></td></tr></table></div></div><h2 id=补充epoch新纪元时间>补充：epoch新纪元时间</h2><p>新纪元时间 Epoch 是以<code>1970-01-01 00:00:00 UTC</code>为标准的时间，将目标时间与<code>1970-01-01 00:00:00</code>时间的差值以秒来计算 ，单位是秒，可以是负值; 有些应用会将时间存储成epoch 时间形式，以提高读取效率。</p><h2 id=参考链接>参考链接</h2><ul><li><a href=https://blog.csdn.net/qq_32157851/article/details/82414443 target=_blank rel="noopener noreffer">postgresql获取系统当前时间毫秒数的sql，以及秒级时间戳</a></li><li><a href=https://www.cnblogs.com/kungfupanda/p/4383882.html target=_blank rel="noopener noreffer">PostgreSQL: epoch 新纪元时间的使用</a></li><li><a href=https://blog.csdn.net/q_l_s/article/details/66974920 target=_blank rel="noopener noreffer">postgresql 比较两个时间差大于 N个小时</a></li><li><a href=https://itbilu.com/database/postgre/E12ilqZXx.html target=_blank rel="noopener noreffer">PostgreSQL中的函数之日期时间函数（使用EXTRACT函数获取日期中的指定值）</a></li></ul><div class=post-reward><input type=checkbox name=reward id=reward hidden>
<label class=reward-button for=reward>赞赏支持</label><div class=qr-code><label class=qr-code-image for=reward><img class=image src=https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master/images/common/wechat.png>
<span>微信打赏</span></label>
<label class=qr-code-image for=reward><img class=image src=https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master/images/common/alipay.png>
<span>支付宝打赏</span></label></div></div></div><div class=post-footer id=post-footer><div class=post-info><div class=post-info-line><div class=post-info-mod><span>更新于 2022-01-09</span></div><div class=post-info-license><span><a rel="license external nofollow noopener noreffer" href=https://creativecommons.org/licenses/by-nc/4.0/ target=_blank>CC BY-NC 4.0</a></span></div></div><div class=post-info-line><div class=post-info-md><span><a class=link-to-markdown href=/posts/postgresql-date/index.md target=_blank>阅读原始文档</a></span></div><div class=post-info-share><span><a href=javascript:void(0); title="分享到 Twitter" data-sharer=twitter data-url=https://lewky.cn/posts/postgresql-date/ data-title="PostgreSQL - 日期函数汇总" data-hashtags=PostgreSQL,工作记录><i class="fab fa-twitter fa-fw"></i></a><a href=javascript:void(0); title="分享到 Facebook" data-sharer=facebook data-url=https://lewky.cn/posts/postgresql-date/ data-hashtag=PostgreSQL><i class="fab fa-facebook-square fa-fw"></i></a><a href=javascript:void(0); title="分享到 Hacker News" data-sharer=hackernews data-url=https://lewky.cn/posts/postgresql-date/ data-title="PostgreSQL - 日期函数汇总"><i class="fab fa-hacker-news fa-fw"></i></a><a href=javascript:void(0); title="分享到 Line" data-sharer=line data-url=https://lewky.cn/posts/postgresql-date/ data-title="PostgreSQL - 日期函数汇总"><i data-svg-src=https://cdn.jsdelivr.net/npm/simple-icons@2.14.0/icons/line.svg></i></a><a href=javascript:void(0); title="分享到 微博" data-sharer=weibo data-url=https://lewky.cn/posts/postgresql-date/ data-title="PostgreSQL - 日期函数汇总"><i class="fab fa-weibo fa-fw"></i></a></span></div></div></div><div class=post-info-more><section class=post-tags><i class="fas fa-tags fa-fw"></i>&nbsp;<a href=/tags/postgresql/>PostgreSQL</a>,&nbsp;<a href=/tags/%E5%B7%A5%E4%BD%9C%E8%AE%B0%E5%BD%95/>工作记录</a></section><section><span><a href=javascript:void(0); onclick=window.history.back();>返回</a></span>&nbsp;|&nbsp;<span><a href=/>主页</a></span></section></div><div class=post-nav><a href=/posts/f20deee6.html/ class=prev rel=prev title="PostgreSQL - 怎么转换数据类型"><i class="fas fa-angle-left fa-fw"></i>PostgreSQL - 怎么转换数据类型</a>
<a href=/posts/eee6409f.html/ class=next rel=next title=页面的背景音乐加载很慢>页面的背景音乐加载很慢<i class="fas fa-angle-right fa-fw"></i></a></div></div><div id=comments><div id=waline></div><script src=https://cdn.jsdelivr.net/npm/@waline/client/dist/Waline.min.js></script><script>new Waline({el:'#waline',meta:["nick","mail","link"],requiredMeta:["nick","mail"],login:"force",placeholder:"为防恶意灌水攻击，评论前需注册并登录，望见谅~",serverURL:"https://comment.lewky.cn/",avatarCDN:"https://sdn.geekzu.org/avatar/",pageSize:20,avatar:"retro",lang:"zh-CN",visitor:true,highlight:true,uploadImage:false,emoji:['https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/嘉然今天吃什么','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/大航海嘉然','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/向晚大魔王','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/贝拉kira','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/珈乐Carol','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/乃琳Queen','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/EveOneCat','https://cdn.jsdelivr.net/gh/walinejs/emojis@1.0.0/weibo','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/滑稽','https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/emoji/default']});</script></div></article></div></main><footer class=footer><div class=footer-container><div class=footer-line><span id=run-time></span>|
<a href=http://rssblog.vercel.app/ target=_blank rel=noopener title=RSSBlog><i class="fas fa-fw fa-inbox"></i>&nbsp;RSSBlog</a></div><div class=footer-line><i class="far fa-copyright fa-fw"></i><span itemprop=copyrightYear>2018 - 2022</span><span class=author itemprop=copyrightHolder>&nbsp;<a href=/ target=_blank>雨临Lewis</a></span>&nbsp;|&nbsp;<span class=license><a rel="license external nofollow noopener noreffer" href=https://creativecommons.org/licenses/by-nc/4.0/ target=_blank>CC BY-NC 4.0</a></span><span class=icp-splitter>&nbsp;|&nbsp;</span><br class=icp-br><span class=icp><a target=_blank href=http://www.beian.miit.gov.cn/ style=font-weight:700>粤ICP备19103822</a></span></div></div></footer></div><div id=fixed-buttons><a href=# id=back-to-top class=fixed-button title=回到顶部><i class="fas fa-arrow-up fa-fw"></i></a><a href=# id=view-comments class=fixed-button title=查看评论><i class="fas fa-comment fa-fw"></i></a></div><div class=sidebar_wo><div id=leimu><img src=https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master/images/b2t/leimuA.png alt=雷姆 onmouseover="this.src='https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/b2t/leimuB.png'" onmouseout="this.src='https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/b2t/leimuA.png'" title=回到顶部></div><div class=sidebar_wo id=lamu><img src=https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master/images/b2t/lamuA.png alt=雷姆 onmouseover="this.src='https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/b2t/lamuB.png'" onmouseout="this.src='https:\/\/cdn.jsdelivr.net\/gh\/lewky\/lewky.github.io@master/images/b2t/lamuA.png'" title=回到底部></div></div><link rel=stylesheet href=https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/css/lightgallery.min.css><script type=text/javascript src=https://cdn.jsdelivr.net/npm/smooth-scroll@16.1.3/dist/smooth-scroll.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/lazysizes@5.2.2/lazysizes.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/lightgallery.js@1.2.0/dist/js/lightgallery.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/lg-thumbnail.js@1.2.0/dist/lg-thumbnail.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/lg-zoom.js@1.2.0/dist/lg-zoom.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/clipboard@2.0.6/dist/clipboard.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/sharer.js@0.4.0/sharer.min.js></script><script type=text/javascript>window.config={"code":{"copyTitle":"复制到剪贴板","maxShownLines":30},"comment":{},"lightGallery":{"actualSize":false,"exThumbImage":"data-thumbnail","hideBarsDelay":2000,"selector":".lightgallery","speed":400,"thumbContHeight":80,"thumbWidth":80,"thumbnail":true}};</script><script type=text/javascript src=/js/theme.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/jquery@2.1.3/dist/jquery.min.js></script><script type=text/javascript src=https://cdn.jsdelivr.net/npm/jquery-backstretch@2.1.18/jquery.backstretch.min.js></script><script>var $cdnPrefix="https://cdn.jsdelivr.net/gh/lewky/lewky.github.io@master";</script><script type=text/javascript src=/js/custom.js></script><script>var _hmt=_hmt||[];(function(){var hm=document.createElement("script");hm.src="https://hm.baidu.com/hm.js?6f278ed0fdb01edb3b1e7398379e5432";var s=document.getElementsByTagName("script")[0];s.parentNode.insertBefore(hm,s);})();</script></body></html>